<?php
/*
 * Copyright (c) Taras Kudla, 2010
 */

# last checking: 15.03.2010, status:worked
class sqlite extends kernel implements dbostruct {

	private $connection= null;
	private $database = null;

	public function init(){
		if (!extension_loaded("sqlite"))
            $this->core->error("DBO_SQLITE", "extension \"sqlite\" not loaded... Cannot work ....");
            return false;
	}

	public function connect($serv, $port, $db, $user, $passwd){
		if($this->core->get("dbopersistent")){
			$this->connection = sqlite_popen($db);
		} else {
			$this->connection = sqlite_open($db);
		}
		if( !$this->connection){
			$this->core->error("DBO_SQLITE","Cannot connect to the database ($db)");
		return false;
		}
		$this->database=$db;
		return true;
	}
    
	public function disconnect(){
		if (is_resource($this->connection)){
			sqlite_close($this->connection);
		}
	}      

	# main sql select method with result handling
	public function select($table, $fields = "*", $sql = null, $type = _RESULT_ASSOC){
		$sql = "select " . $fields . " from " . $table . " " . $sql;
		
		if( $type==_RESULT_ASSOC){
			return sqlite_array_query($this->connection,$sql,SQLITE_ASSOC);
		}	
		$q = @sqlite_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_SELECT", (is_resource($this->connection) ? sqlite_last_error($this->connection) : null));
			return false;
		}
		switch ($type){
            case _RESULT_ASSOC:
                return $this->sqlToAssoc($q);
                break;
            case _RESULT_LIST:
                return $this->sqlToList($q, $fields);
                break;
            case _RESULT_BOOL:
                return ((bool) sqlite_num_rows($q));
                break;
            case _RESULT_PAIRS:
                return $this->sqlToPairs($q);
                break;
            case _RESULT_NUMROW:
                return $this->sqlToNumRow($q);
                break;		
            case _RESULT_ROW:
                return $this->sqlToRow($q);
                break;
            case _RESULT_SINGLE:
	    	case _RESULT_ONE:
	    		return $this->sqlToSingle($q);
	    		break;	    
            default:
                return $this->sqlToAssoc($q);
        }
	}
	# sql result to simple array ( as list )
	private function &sqlToList(&$q, $fields){
		if (preg_match("/[,]/", $fields, $null)){
			$this->core->error("DBO_DRIVER_SELECT", "Error in results ordering as LIST. Field should be without comas.");
		return false;
		}
		$a = array();
		while($row=sqlite_fetch_array($q,SQLITE_NUM)){
			$a[] = $row[0];
		}
		return $a;
	}
      

	private function sqlToSingle(&$q){
    	if( sqlite_num_rows($q) == 1 && sqlite_num_fields($q)== 1 ){
    		$q=sqlite_fetch_array($q,SQLITE_NUM);
    		return $q[0];
    	}   
    	return null; 
    }

    # sql select result as associative array ( like dictionary in python )
	private function sqlToRow(&$q){
		if (sqlite_num_rows($q) == 0)
			return array();
		return sqlite_fetch_array($q,SQLITE_ASSOC);
	}

	# sql select result as numeric indexes array
	private function sqlToNumRow(&$q){
		if (sqlite_num_rows($q) == 0)
			return array();
		return sqlite_fetch_array($q,SQLITE_NUM);
	}
	# sql select result to array of pairs key=>value
	private function &sqlToPairs(&$q){
		$array = array();
		if (sqlite_num_fields($q) != 2){
			$this->core->error("DBO", "Cannot build pairs for result. Fields number must be 2");
		return false;
		}
		while ($row = sqlite_fetch_array($q,SQLITE_NUM)){
			$array[$row[0]] = $row[1];
		}
		return $array;
	}	

	# sql select result to arrays of associative array
	private function &sqlToAssoc(&$q){
		//$array = array();
		return sqlite_array_query($this->connection,$sql,SQLITE_ASSOC);
		//$num = sqlite_num_rows($q);
		//for ($i = 0; $i < $num; $i++){
			//$array[$i] = sqlite_fetch_array($q,SQLITE_ASSOC);
		//}
		//return $array;
	}

	# inserting records to sql table
	public function insert($table, array $data,$returnid=null){
		if (!is_array($data) || count($data) < 1){
			return false;
		}
		$q = @sqlite_query($this->connection,"INSERT INTO $table (".implode(",",array_keys($data)).") VALUES ('".implode("','",array_values($data))."')");
		if ($q == false){
			$this->core->error("DBO_DRIVER_INSERT", sqlite_last_error($this->connection));
		return false;
		}
		return true;
	}

	# updating table
	public function update($table, $sql){
		$sql = "update " . $table . " " . $sql;
		$q = @sqlite_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_UPDATE", sqlite_last_error($this->connection));
		return false;
		}
		return true;
	}

    # deleting from table  
	public function delete($table, $sql){
		$sql = "delete from " . $table . " " . $sql;
		$q = @sqlite_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DELETE", sqlite_last_error($this->connection));
		}
		return $q;
	}

	# direct sql query
	public function execute($sql){
		$q = @sqlite_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DIRECT", sqlite_last_error($this->connection));
			return false;
		}
		return $q;
	}   

	# creating a table
    public function createtable(sqltable $map){
    	
      	list($name,$fields,$keys,$encoding,$engine) = $map->get();    	
      	$pkeys =array();
      	foreach( $keys as $k=>$v ){
      		if( $v[0]==_KEY_PRIMARY) $pkeys[]=$v[1];
      	}
      	$text = "CREATE TABLE $name ( ";
      	# fields processing      	
      	foreach( $fields as $k=>$v){
      		list($fname,$ftype,$fsize,$fdefault,$fnull) = $v;
      		$fresult = $this->types($ftype,$fsize);
      		if( $ftype==_FIELD_TEXT && is_null($fdefault)) {
      			$default = " ";
      		} else if( ( $ftype==_FIELD_INT || $ftype==_FIELD_BIGINT || $ftype==_FIELD_TINYINT )  && is_null($fdefault)){
      			$default =" DEFAULT '0' ";
      		}  else {
      			$default =  !is_null($fdefault)?" DEFAULT '".$this->core->escapeString($fdefault)."' ":($ftype==_FIELD_INTAUTO?null:" ".($fnull?"NULL":"NOT NULL")." ");
      		}
      		$text .=($k==0?null:" ,")." $fname $fresult ".($default).( in_array($fname,$pkeys)?" PRIMARY KEY ":null ) ." ";
      	}
      	$text .=" ) ";
      	if( $map->droponexist){
      		if( $this->tableexists($name)){
      			$this->execute("DROP TABLE $name");
      		}
      	}
      	$this->execute($text);  	
      	# keys processing
      	foreach( $keys as $k=>$v){
      		$query = null;
      		list($ktype,$kfield) = $v;
      		$keydata = null;      		
      		switch( $ktype ){
      			case _KEY_INDEX  : $keydata = "INDEX"; break;
      			case _KEY_UNIQUE : $keydata = "UNIQUE INDEX"; break;
  			
      		}    		
      		if( !is_null($keydata)){
      			
      			$query = "CREATE $keydata $name"."_"."$kfield ON $name ( ".$kfield." ) ";
      			$this->execute($query);
      			
      		}
      	}
      	return true;
      	      	
	}      
    
	public function gettables($database=null){
		$q = $this->execute("SELECT * FROM sqlite_master WHERE type='table'");
		$result = array();
		while( $row=sqlite_fetch_array($q,SQLITE_ASSOC)){
			$result[]=$row["name"];
		}
		return $result;
	}
	
	public function getdatabases(){
		return array($this->database);
	}
	public function getdatabase(){
		return $this->database;
	}	
	
	public function getfields($table){
		$struct = sqlite_array_query($this->connection, "PRAGMA table_info('$table')", SQLITE_ASSOC);
		for ($i=0,$names=array();$i<sizeof($struct);++$i){
			$type=$struct[$i]["type"];
			if( preg_match("/^([A-z0-9_]+)\(([0-9\,]+)\)$/",$type,$tmp)){
				$size = $tmp[2];
				$type=$tmp[1];
			}
			else $size = null;
			$type = $this->typesback(strtolower($type));
			//var_dump($struct[$i]["pk"]);
			if(($type == _FIELD_INT || $type==_FIELD_BIGINT) && $struct[$i]["pk"]=="1")
				$type = _FIELD_INTAUTO;
			$null = ( $struct[$i]["notnull"]=="99" )?false:true;			
			$fields[]=array("name"=>$struct[$i]["name"],"type"=>$type,"default"=>$struct[$i]["dflt_value"],"size"=>$size,"null"=>$null);
		}
		return $fields;
	}

	public function getkeys($table){
		//$struct = sqlite_array_query($this->connection, "SELECT  FROM sqlite_master WHERE tbl_name='$table' and type='index'", SQLITE_ASSOC);
		//$keys = array();
		//foreach ( $struct as $k=>&$v){
			//$keyname = $v["name"];
			//if( preg_match("/$table\_([A-z0-9_]+)/",$keyname,$tmp))
				//$keyname=$tmp[1];
			//if( preg_match("/UNIQUE/",$v["sql"]))			
		//}
		
		//var_dump($struct);		
	}
	
	
	public function tableexists($name){
		return in_array($name,$this->gettables());
	}

	private function types($type, $size = null){
		$result = null;
        switch ($type)
        {
        	case _FIELD_BIGINTAUTO:
            case _FIELD_INTAUTO:
                $result = " INTEGER PRIMARY KEY";
                break;
            case _FIELD_INT:
                $result = " INTEGER".($size != null?"(".$size.")":"(11)");
                break;
            case _FIELD_SMALLINT:
            	$result = " INTEGER".($size != null?"(".$size.")":"(6)");
            case _FIELD_TINYINT:
                $result = " INTEGER".($size != null?"(".$size.")":"(4)");
                break;
            case _FIELD_BIGINT:
                $result =" INTEGER".($size != null?"(".$size.")":"(20)");
                break;
            case _FIELD_CONSTCHAR:
                $result = "CHAR(".($size!=null?(int)$size : "255").") ";
                break;            	
            case _FIELD_CHAR:
                $result = "VARYING CHARACTER(" . ($size!= null?(int)$size : "255") . ") ";
                break;
            case _FIELD_DATE:
                $result = "DATE";
                break;
            case _FIELD_REAL:
            	$result = " REAL".($size != null?"(".$size.")":null);
            	break;
            case _FIELD_DOUBLE:
            	$result = " DOUBLE".($size != null?"(".$size.")":null);
            	break;
            case _FIELD_FLOAT:
            	$result = "FLOAT".($size != null?"(".$size.")":null);
            	break;        	
            case _FIELD_DECIMAL:
            	$result = " NUMERIC(".($size!=null?$size : "10,5").")";
            	break;
            case _FIELD_TEXT:
                $result = "TEXT ";
                break;
            case _FIELD_LONGTEXT:
                $result = "TEXT ";
                break;
            case _FIELD_DATETIME:
                $result = " DATETIME ";
                break;
            case _FIELD_BLOB:
                $result = " BLOB ";
                break;
            case _FIELD_TIME:
                $result = " TIME ";
                break;
            case _FIELD_BOOL:
                $result = " BOOLEAN ";
                break;
            case _FIELD_BIT:
            	$result = " INT(1) ";
            	break;
            case _FIELD_TIMESTAMP:
                $result = " TIMESTAMP ";
                break;
            default:
                "CHAR(255)";
                break;
        }
        return $result;
      }
            
      private function typesback($type){
        $result = null;
        switch ($type)
        {
        	case "smallint":
        		$result = _FIELD_SMALLINT;
        		break;
        	case "mediumint":
        	case "integer":
            case "int":
                $result = _FIELD_INT;
                break;
            case "tinyint":
                $result = _FIELD_TINYINT;
                break;
            case "bigint":
                $result = _FIELD_BIGINT;
                break;
            case "char":
            	$result = _FIELD_CONSTCHAR;
            	break;
            case "nationalvaryingcharacter":
            case "varchar":
            case "nvarchar":
            case "varyingcharacter":
                $result = _FIELD_CHAR;
                break;
            case "date":
                $result = _FIELD_DATE;
                break;
            case "datetime":
                $result = _FIELD_DATETIME;
                break;
            case "timestamp":
                $result = _FIELD_TIMESTAMP;
                break;
            case "time":
                $result = _FIELD_TIME;
                break;
            case "float":
            	$result = _FIELD_FLOAT;
            	break;
            case "real":
            	$result = _FIELD_REAL;
            	break;
            case "double":
            	$result = _FIELD_DOUBLE;
            	break;
            case "decimal":
            case "numeric":
            	$result = _FIELD_DECIMAL;
            	break;
            case "bool":
            	$result = _FIELD_BOOL;
            	break;
            case "blob":
            	$result = _FIELD_BLOB;
            	break;
            case "bit":
            	$result = _FIELD_BIT;
            	break;
            case "tinytext":
            case "mediumtext":
            case "text":
            case "longtext":
                $result = _FIELD_TEXT;
                break;
            default:
                $result=$type;
                break;
        }
        return $result;
      }
      
      
	public function version(){
		return sqlite_libversion();
	}
	
	public function droptable($tablename){
	    if( is_object($tablename) && get_class($tablename)=="sqltable"){
    		$tablename = $tablename->getname();
    	}
		$this->execute("DROP TABLE $tablename");
	}
      
}
?>